﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Apollo.Web;
using NHibernate.Dialect;
using NHibernate.Tool.hbm2ddl;
using System.Data.Common;

namespace Apollo.Website
{
    public partial class DatabaseManager : System.Web.UI.Page, IApolloPage
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                CreateSQLScripts();
            }
        }

        protected void CreateSQLScripts()
        {
            NHibernate.Cfg.Configuration config = Apollo.Application.Configuration;

            Dialect dialect = Dialect.GetDialect(config.Properties);

            RepeaterSQLScripts.DataSource = config.GenerateSchemaUpdateScript(
                    dialect
                    , new DatabaseMetadata((DbConnection)Apollo.Application.CurrentSession.Connection, dialect)
                )
                .Where<string>(s => !s.StartsWith("create table v_")); // ignore views
            RepeaterSQLScripts.DataBind();
        }

        protected void RepeaterSQLScripts_ItemDataBound(object sender, RepeaterItemEventArgs e)
        {
            Label LabelSQL = e.Item.FindControl("LabelSQL") as Label;
            HiddenField HiddenFieldSQL = e.Item.FindControl("HiddenFieldSQL") as HiddenField;
            CheckBox CheckBoxRunSQL = e.Item.FindControl("CheckBoxRunSQL") as CheckBox;

            if (LabelSQL == null || HiddenFieldSQL == null || CheckBoxRunSQL == null) return;

            string sqlString = (string)e.Item.DataItem;


            if (sqlString.StartsWith("create table"))
            {
                LabelSQL.Text = "Create table <b><i>"
                    + sqlString.Substring(13, sqlString.IndexOf("(") - 14)
                    + "</i></b>";


                HiddenFieldSQL.Value = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].["
                    + sqlString.Substring(13, sqlString.IndexOf("(") - 14)
                    + "]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)"
                    + sqlString;
            }
            else if (sqlString.Contains(" add constraint"))
            {
                LabelSQL.Text = "Add relationship between <b><i>"
                    + sqlString.Substring(11, sqlString.IndexOf(" add constraint") - 11)
                    + "</i></b> and <b><i>"
                    + sqlString.Substring(sqlString.LastIndexOf(" "))
                    + "</i></b>";

                HiddenFieldSQL.Value = sqlString;
            }
            else
            {
                LabelSQL.Text = sqlString;
                HiddenFieldSQL.Value = sqlString;
            }
        }

        protected void ButtonSave_Click(object sender, EventArgs e)
        {
            string sqlQuery = "";

            foreach (RepeaterItem ri in RepeaterSQLScripts.Items)
            {
                HiddenField HiddenFieldSQL = ri.FindControl("HiddenFieldSQL") as HiddenField;
                CheckBox CheckBoxRunSQL = ri.FindControl("CheckBoxRunSQL") as CheckBox;

                if (HiddenFieldSQL == null || CheckBoxRunSQL == null) continue;

                if (!CheckBoxRunSQL.Checked || !CheckBoxRunSQL.Enabled) continue;

                sqlQuery += HiddenFieldSQL.Value + "; \n\n";

                CheckBoxRunSQL.Enabled = false;
            }

            try
            {
                NHibernate.ISession session = Apollo.Application.CurrentSession;

                NHibernate.ISQLQuery query = session.CreateSQLQuery(sqlQuery);

                query.ExecuteUpdate();

                LabelWarn.Text = "Tables were generated";
                LabelWarn.CssClass = "warn";
                LabelWarn.Visible = true;
                ImageWarn.Visible = true;
                ImageWarn.ImageUrl = "/css/icons/icon_40.png";
                entity_header.Visible = true;
            }
            catch (Exception ex)
            {
                LabelWarn.Text = "Action failed! Reason: " + ex.Message;
                LabelWarn.CssClass = "error";
                LabelWarn.Visible = true;
                ImageWarn.Visible = true;
                ImageWarn.ImageUrl = "/css/icons/error.png";
                entity_header.Visible = true;
            }
        }


        public int SelectedMenuItemID
        {
            get { return Apollo.Application.Meta<Apollo.MenuItem>().Single<Apollo.MenuItem>(mi => mi.Name == "Database manager").Id; }
        }

        public int SelectedRootMenuItemId
        {
            get { return Apollo.Application.Meta<Apollo.MenuItem>().Single<Apollo.MenuItem>(mi => mi.Name == "Settings").Id; }
        }
    }
}